package mobi.weq.dao;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import mobi.weq.db.Database;
import mobi.weq.model.Observation;
import mobi.weq.util.Util;

public class ObservationDao {
	public synchronized int addObservations(Observation ob) throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into observation(id,NO2,CO,SO2,O3,pm10,pm2p5,user_categorization,user_comments,image_path,image_name,image_description,latitude,longitude,altitude,time_stamp) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setString(1, ob.getId());
		ps.setDouble(2, ob.getNo2());
		ps.setDouble(3, ob.getCo());
		ps.setDouble(4, ob.getSo2());
		ps.setDouble(5, ob.getO3());
		ps.setDouble(6, ob.getPm10());
		ps.setDouble(7, ob.getPm2p5());
		ps.setString(8, ob.getUser_categorization());
		ps.setString(9, ob.getUser_comments());
		ps.setString(10, ob.getImage_path());
		ps.setString(11, ob.getImage_name());
		ps.setString(12, ob.getImage_description());
		ps.setDouble(13, ob.getLatitude());
		ps.setDouble(14, ob.getLongitude());
		ps.setDouble(15, ob.getAltitude());
		Date d = ob.getDatetime();
		if (d != null)
			ps.setTimestamp(16, new java.sql.Timestamp(d.getTime()));
		else
			ps.setDate(16, null);

		return ps.executeUpdate();

	}

	public List<Observation> getAllObservations() throws SQLException {

		Connection c = Database.getConnection();
		String query = "select * from observation";
		PreparedStatement ps = c.prepareStatement(query);

		ResultSet rs = ps.executeQuery();
		List<Observation> observations = new ArrayList<Observation>();

		while (rs.next()) {
			Observation ob = new Observation();
			ob.setId(rs.getString(1));
			ob.setNo2(rs.getDouble(2));
			ob.setCo(rs.getDouble(3));
			ob.setSo2(rs.getDouble(4));
			ob.setO3(rs.getDouble(5));
			ob.setPm10(rs.getDouble(6));
			ob.setPm2p5(rs.getDouble(7));
			ob.setUser_categorization(rs.getString(8));
			ob.setUser_comments(rs.getString(9));
			ob.setImage_path(rs.getString(10));
			ob.setImage_name(rs.getString(11));
			ob.setImage_description(rs.getString(12));
			ob.setLatitude(rs.getDouble(13));
			ob.setLongitude(rs.getDouble(14));
			ob.setAltitude(rs.getDouble(15));
			ob.setDatetime(rs.getTimestamp(16));
			observations.add(ob);
		}
		return observations;
	}

	public synchronized int deleteObservation(Observation ob)
			throws SQLException {

		Connection cn = Database.getConnection();
		String sql = "delete from observation where ?=?";
		PreparedStatement ps = cn.prepareStatement(sql);
		return ps.executeUpdate();
	}

	public static void main(String[] args) throws SQLException,
			IllegalAccessException, IllegalArgumentException,
			InvocationTargetException {
		Observation ob = new Observation();
		ob.setCo(44.3);
		ob.setPm10(3);
		ob.setSo2(5.6776);
		ObservationDao od = new ObservationDao();

		od.addObservations(ob);

		List<Observation> list = od.getAllObservations();
		for (Observation observation : list) {
			Util.printObject(observation);
		}
	}
}
